# 生成db数据库
import sqlite3

# 连接到SQLite数据库
conn = sqlite3.connect('db.db')

# 创建一个Cursor对象，用于执行SQL语句
cursor = conn.cursor()

# 创建一个表
cursor.execute('''CREATE TABLE my_table
                  (id CHAR(36) PRIMARY KEY, name TEXT, age INTEGER)'''
)

# 插入一些数据
cursor.execute("INSERT INTO my_table (name, age) VALUES (?, ?)", ('Alice', 25))
cursor.execute("INSERT INTO my_table (name, age) VALUES (?, ?)", ('Bob', 30))


# 查询数据
cursor.execute("SELECT * FROM my_table")
rows = cursor.fetchall()

# 打印查询结果
for row in rows:
    print("echo:", row)


# 更新数据
cursor.execute("UPDATE my_table SET age=? WHERE name=?", (35, 'Alice'))
cursor.execute("SELECT * FROM my_table")
rows = cursor.fetchall()

# 打印查询结果
for row in rows:
    print("change age:", row)


# 删除数据
cursor.execute("DELETE FROM my_table WHERE name=?", ('Bob',))
cursor.execute("SELECT * FROM my_table")
rows = cursor.fetchall()

# 打印查询结果
for row in rows:
    print(row)

# 依据主键更新数据
cursor.execute("UPDATE my_table SET age=? WHERE id=?", (40, 1)) 
cursor.execute("SELECT * FROM my_table")
rows = cursor.fetchall()

# 打印查询结果
for row in rows:
    print(row)

# 给主键赋值
cursor.execute("INSERT INTO my_table (id, name, age) VALUES (?, ?, ?)", (2, 'Charlie', 35))
cursor.execute("SELECT * FROM my_table")
rows = cursor.fetchall()

# 打印查询结果
for row in rows:
    print(row)
    
# 删除表
# cursor.execute("DROP TABLE my_table")

# 生成uuid并依据主键更新数据
import uuid

# 插入一些数据
cursor.execute("INSERT INTO my_table (id, name, age) VALUES (?, ?, ?)", (str(uuid.uuid4()), 'David', 40))
cursor.execute("SELECT * FROM my_table")
rows = cursor.fetchall()

# 依据主键更新数据
cursor.execute("UPDATE my_table SET age=? WHERE id=?", (45, 2))
cursor.execute("SELECT * FROM my_table")
rows = cursor.fetchall()

# 打印查询结果
for row in rows:
    print(row)
    

# 删除表
cursor.execute("DROP TABLE my_table")

# 提交更改
conn.commit()

conn.close()